﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;


namespace Human
{
    public partial class frmChuyenData : Form
    {
        AccessData mDB = new AccessData();
        Language lan = new Language();
        string ConSt = "Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=Data\\nhansu.mdb;Jet OLEDB:Database Password=links7155019;";
        OleDbConnection con;      
        public frmChuyenData()
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (chkbp.Checked)
                dm_bp();
            if (chkCd.Checked)
                dm_cd();
            if (chkCV.Checked)
                dm_cvu();
            if (ngach.Checked)
                dm_ngach();
            if (chkLlnv.Checked)
                Lylichnv();
            if (chkHD.Checked)
                capnhat_HD();
            if (chkCongtac.Checked)
                Congtac();
            if (chkDt.Checked)
                DaoTao();
            if (chkNganh.Checked)
                Ctdaotao();
            if (chkBancap.Checked)
                Bancap();
            if (chkGc.Checked)
                giacanh();
            if (chkCapso.Checked) capso();
            if (chkDthe.Checked) doanthe();
            if (chkHocvan.Checked) hocvan();
            if (chkHdt.Checked) hedaotao();
            if (chk_hsdt1.Checked) hs_dt1();
        }
        private void hs_dt1()
        {
            string sql = "";
            decimal hs_dt = 0;
            sql = "select hs_dt1,manv from hopdong ";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                hs_dt = (r["hs_dt1"].ToString() != "") ? decimal.Parse(r["hs_dt1"].ToString()) : 0;
                sql = "update " + mDB.user + ".hopdong set hs_dt1="+hs_dt;
                sql += " where manv='" + r["manv"].ToString() + "'";
                mDB.Execute_data(sql);
                i++;
                this.Text = "cap nhat: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            MessageBox.Show("OK");
        }
        private void doanthe()
        {
            //string sql = "", tamthan = "821.3.01", dnai = "713.3.03";    
            //sql=" SELECT mnvmay,manv,tp_x_than,tp_b_than,nguongoc,";
            //sql+=" format(ngay_t_gia,'dd/mm/yyyy')as m_ngay_t_gia,format(ngay_t_ly,'dd/mm/yyyy') as m_ngay_t_ly,";
            //sql+=" format(dubi_dang,'dd/mm/yyyy') as m_dubi_dang, format(cthuc_dang,'dd/mm/yyyy') as m_cthuc_dang,";
            //sql+=" chinhtri,format(ngay_tn,'dd/mm/yyyy') as m_ngay_tn,format(ngay_doan,'dd/mm/yyyy') as m_ngay_doan FROM llnv order by manv";
            //OleDbCommand cmd = new OleDbCommand(sql, con);
            //OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            //DataSet ds = new DataSet();
            //dest.Fill(ds);
            //int i = 0, rec = 0;
            //rec = ds.Tables[0].Rows.Count;
            //string st_bhyt, nc_bhyt, t_bhyt, tht_bhyt, d_bhyt, tbv_bhyt="",
            //    bhtn24_bhyt, bhnt_bhyt, bhk_bhyt ;
            //foreach (DataRow r in ds.Tables[0].Rows)
            //{
            //    mDB.upd_lylichnvDoanThe(r["manv"].ToString(),r["chinhtri"].ToString(),r["nguongoc"].ToString(),
            //        r["m_ngay_t_gia"].ToString(),r["m_ngay_t_ly"].ToString(),r["m_ngay_doan"].ToString(),r["m_dubi_dang"].ToString(),r["m_cthuc_dang"].ToString(),
            //        r["tp_x_than"].ToString(),r["tp_b_than"].ToString(),r["m_ngay_tn"].ToString());
            //    i++;
            //    this.Text = "Chuyển đoàn thể: " + i.ToString() + "/" + rec.ToString() + " dòng";
            //    this.Update();
            //}
            //cmd.Dispose();
            ////
            //ds = new DataSet();
            //sql=" SELECT MANV,MANVMAY,DANG,THEDANG,format(CHUYEN_DEN,'dd/mm/yyyy') as m_chuyen_den,";
            //sql+=" CHI_BO, CONG_DOAN,TO_t,THE_CDOAN, format(CDOANPHI,'dd/mm/yyyy') as m_cdoanphi, ";
            //sql += " format(VAO_CDOAN,'dd/mm/yyyy') as m_vao_cdoan,DOAN, format(DOAN_DEN,'dd/mm/yyyy') as m_doan_den, ";
            //sql += " TODOAN,THEDOAN, format(DOANPHI,'dd/mm/yyyy') as m_doanphi, MAX_DANG,MAX_DOAN,MAX_CQUYEN,CMAX_DANG,";
            //sql += " CMAX_DOAN,CMAX_QUYEN FROM hopdong ";
            //cmd = new OleDbCommand(sql, con);
            //dest = new OleDbDataAdapter(cmd);
            //dest.Fill(ds);
            //i = 0; rec = 0;
            //rec = ds.Tables[0].Rows.Count;
            //foreach (DataRow r in ds.Tables[0].Rows)
            //{
            //    mDB.upd_hopdongDoanThe(r["manv"].ToString(), r["dang"].ToString(),
            //        r["thedang"].ToString(), r["m_chuyen_den"].ToString(), r["chi_bo"].ToString(),
            //        r["cong_doan"].ToString(), r["to_t"].ToString(), r["THE_CDOAN"].ToString(),
            //        r["m_cdoanphi"].ToString(), r["m_vao_cdoan"].ToString(), r["doan"].ToString(),
            //        r["m_doan_den"].ToString(), r["todoan"].ToString(), r["thedoan"].ToString(),
            //        r["m_doanphi"].ToString(), r["max_dang"].ToString(), r["max_doan"].ToString(),
            //        r["MAX_CQUYEN"].ToString(), r["CMAX_DANG"].ToString(), r["CMAX_DOAN"].ToString(), r["CMAX_QUYEN"].ToString());
                    
            //    i++;
            //    this.Text = "Chuyển đoàn thể: " + i.ToString() + "/" + rec.ToString() + " dòng";
            //    this.Update();
            //}
            //cmd.Dispose();


        }
        private void capso()
        {
            mDB.Execute_data("delete from nhansu.thebhyt");
            string sql = "", tamthan = "701.4.59";            		
            sql = "select a.*,format(a.ngaycap,'dd/mm/yyyy') as m_ngaycap,format(a.thoihan,'dd/mm/yyyy') as m_thoihan,";
            sql+=" format(a.denhan,'dd/mm/yyyy') as m_denhan from thebhyt a order by manv";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            string st_bhyt, nc_bhyt, t_bhyt, tht_bhyt, d_bhyt, tbv_bhyt="",
                bhtn24_bhyt, bhnt_bhyt, bhk_bhyt ;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                st_bhyt = r["sobhyt"].ToString();
                nc_bhyt = r["m_ngaycap"].ToString();
                t_bhyt = r["captai"].ToString().Trim();
                tht_bhyt = r["m_thoihan"].ToString();
                d_bhyt = r["m_denhan"].ToString();
                switch (r["tai_bvien"].ToString().Trim())
                {
                    case "TQ2":
                        tbv_bhyt = tamthan;
                        break;
                    case "GD":
                        tbv_bhyt = "701.3.10";
                        break;
                }
                bhtn24_bhyt = r["bhtn"].ToString();
                bhnt_bhyt = r["bhnhantho"].ToString();
                bhk_bhyt = "";
                mDB.upd_BaohiemYteCS(r["manv"].ToString(), st_bhyt, nc_bhyt, t_bhyt, tht_bhyt, d_bhyt, tbv_bhyt, bhtn24_bhyt, bhnt_bhyt, bhk_bhyt);
                i++;
                this.Text = "Chuyển BHYT: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            // so ld

            //   bhxh
            //sql = "INSERT INTO " + user + ".llnv(mnvmay,sold,ngaysold,";
            //sql += " tinhsld,huyensld,dkldso,ngaydkld,ngaycap,matinhbhxh)";

            sql = "select a.manv,sobhxh,k_tinhbhxh,sld_tudau, mnvmay,sold,format(b.ngaysold,'dd/mm/yyyy') as m_ngaysold,";
            sql += " tinhsld,huyensld,dkldso,format(b.ngaydkld,'dd/mm/yyyy') as m_ngaydkld,format(ngaycap,'dd/mm/yyyy') as m_ngaycap,matinhbhxh ";
            sql += ",sld_ngaydi,tgbhxh from hopdong a,llnv b where a.manv=b.manv order by a.manv";
            cmd = new OleDbCommand(sql, con);
            dest = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            dest.Fill(ds);
            i = 0; rec = 0;
            rec = ds.Tables[0].Rows.Count;
            int stktbhxh_bhxh = 0;// (txtSTKTBHXH_CS.Text.ToString() == "") ? 0 : int.Parse(txtSTKTBHXH_CS.Text.ToString());
            decimal tgbhxh_bhxh = 0;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.upd_lylichnvCS(r["manv"].ToString(),r["sold"].ToString(),r["m_ngaysold"].ToString(),
                    r["tinhsld"].ToString(), r["huyensld"].ToString(),r["dkldso"].ToString(),
                    r["m_ngaydkld"].ToString(),r["m_ngaycap"].ToString(), r["matinhbhxh"].ToString());
                //
                stktbhxh_bhxh = (r["k_tinhbhxh"].ToString() == "") ? 0 : int.Parse(r["k_tinhbhxh"].ToString());
                tgbhxh_bhxh=(r["tgbhxh"].ToString() == "") ? 0 : int.Parse(r["tgbhxh"].ToString());
                mDB.upd_hopdongCS(r["manv"].ToString(),r["sobhxh"].ToString(), stktbhxh_bhxh, "", "", tgbhxh_bhxh);

                i++;
                this.Text = "Chuyển BHXH+SLD: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            

        }
        private void Ctdaotao()
        {
            mDB.Execute_data("delete from nhansu.ctdaotao");
            string sql = "";
            sql = "select * from ctdaotao";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_CTDAOTAO(r["mactdt"].ToString(), r["tenctdt"].ToString(), "01");
                i++;
                this.Text = "Chuyển Ngành đt: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }

        }
        private void hocvan()
        {
            mDB.Execute_data("delete from nhansu.hocvan");
            string sql = "";
            sql = "select * from hocvan";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.upd_DMHOCVAN(r["mahv"].ToString(), r["tenhv"].ToString());
                i++;
                this.Text = "Học vấn: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }

        }
        private void hedaotao()
        {
            mDB.Execute_data("delete from nhansu.hedaotao");
            string sql = "";
            sql = "select * from hedaotao";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Execute_data("insert into nhansu.hedaotao(mahe,tenhe) values('"+r["mahe"].ToString()+"','"+ r["tenhe"].ToString()+"')");
                i++;
                this.Text = "Hệ đào tạo: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }

        }
        private void Bancap()
        {
            mDB.Execute_data("delete from nhansu.bangcap");
            string sql = "";
            sql = "select * from bangcap";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.upd_DMBANGCAP(r["mabangcap"].ToString(), r["tenbang"].ToString(), r["mabc"].ToString(),Int32.Parse(r["stt"].ToString()));
                i++;
                this.Text = "Chuyển bằng cấp: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }

        }
        private void giacanh()
        {
            string sql = "select  a.manv,a.lietsi,";
            sql += " a.hoan_canh,b.dacdiem  from llnv a ,hopdong b where a.manv=b.manv order by a.manv";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.upd_giacanhllnv(r["manv"].ToString(), (r["lietsi"].ToString() == "1") ? 1 : 0, (r["hoan_canh"].ToString() == "1") ? 1 : 0,(r["thuongbinh"].ToString()=="1") ?1:0);
                mDB.upd_giacanhhopdong(r["manv"].ToString(), r["dacdiem"].ToString());
                i++;
                this.Text = "Chuyển thông tin gia cảnh: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            mDB.Execute_data("delete from  nhansu.giacanh order by manv");
            sql = "select  * from  giacanh order by manv";
            cmd = new OleDbCommand(sql, con);
            dest = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            dest.Fill(ds);
            i = 0; rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                i++;
                mDB.upd_giacanh(i, r["manv"].ToString(), r["htendtg"].ToString(), r["madt"].ToString(), r["phai"].ToString(), r["namsinh"].ToString(), r["quequan"].ToString(), r["nghenghiep"].ToString(), r["ghichu"].ToString(),"");                   
                this.Text = "Chuyển chi tiết gia cảnh: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
        }
        private void DaoTao()
        {
            //Ly lich dao tao
            string sql = "";
            sql = "select manv,  ";
            sql += " mann, mann1,";
            sql += " mann2,mann3,";
            sql += " mahv,nnghiep,td_nnghiep , he_dt, tg_dt, tdnn, tdnn1, tdnn2, tdnn3, tdth, mahocham  from llnv order by manv ";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.upd_llnvdaotao(r["manv"].ToString(), r["mann"].ToString(), r["mann1"].ToString(), r["mann2"].ToString(),
                                    r["mann3"].ToString(), r["mahv"].ToString(), r["nnghiep"].ToString(), r["namtn_cm"].ToString(), r["td_nnghiep"].ToString(),
                                    r["he_dt"].ToString(),(r["tg_dt"].ToString()!=""?int.Parse(r["tg_dt"].ToString()):0),
                                    r["tdnn"].ToString(), r["tdnn1"].ToString(), r["tdnn2"].ToString(),
                                    r["tdnn3"].ToString(), r["tdth"].ToString(), r["mahocham"].ToString());
                i++;
                this.Text = "Chuyển LL_DT: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
                
            }
            //
            DataSet ds1 = new DataSet();
            sql=" select manv, namqlnn,tdqlnn from hopdong order by manv";
            cmd = new OleDbCommand(sql, con);
            dest = new OleDbDataAdapter(cmd);
            dest.Fill(ds1);
            rec = ds1.Tables[0].Rows.Count;
            i = 0;
            foreach (DataRow r1 in ds1.Tables[0].Rows)
            {
                mDB.upd_hopdong_daotao(r1["manv"].ToString(),r1["namqlnn"].ToString(),r1["tdqlnn"].ToString());
                i++;
                this.Text = "Chuyển HĐ_DT: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            
            ds1 = new DataSet();
            sql = " select a.*,format(tungay,'dd/mm/yyyy') as tungay_dt, format(denngay,'dd/mm/yyyy') as denngay_dt from qtdaotao a order by manv";
            cmd = new OleDbCommand(sql, con);
            dest = new OleDbDataAdapter(cmd);
            dest.Fill(ds1);
            rec = ds1.Tables[0].Rows.Count;
            i = 0;
            foreach (DataRow r1 in ds1.Tables[0].Rows)//decimal id, string donvi,string ten_nd,string noidt,string user
            {
                mDB.upd_qtdaotao(r1["manv"].ToString(),r1["mahv"].ToString(),r1["mabangcap"].ToString(),r1["hehocvan"].ToString(),
                    r1["thang"].ToString(), r1["tungay_dt"].ToString(),r1["tu_nam"].ToString(), r1["denngay_dt"].ToString(),r1["den_nam"].ToString(),r1["ghichu"].ToString(),decimal.Parse( r1["id"].ToString()),r1["donvi"].ToString(),r1["ten_nd"].ToString(),r1["user"].ToString(),"");
                i++;
                this.Text = "Chuyển QT_DT: " + i.ToString() + "/" + rec.ToString() + " dòng";
                this.Update();
            }
            MessageBox.Show("Xong!");
            
        }
        private void Congtac()
        {

            OleDbCommand cmd = new OleDbCommand("select a.*,format(ngayhl,'dd/mm/yyyy') as tu_ngay,format(denngay,'dd/mm/yyyy') as den_ngay from congviec a order by manv", con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            DataSet ds1 = new DataSet();
            dest.Fill(ds);
            int i = 0, rec = 0;
            rec = ds.Tables[0].Rows.Count;
            try
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    i++;
                    mDB.upd_congviec(i, r["manv"].ToString(), (r["tu_ngay"].ToString() != "" ? r["tu_ngay"].ToString() : ""),r["tu_nam"].ToString(),
                        (r["den_ngay"].ToString() != "" ? r["den_ngay"].ToString() : ""),r["den_nam"].ToString(), r["donvi"].ToString()
                        , r["mucluong"].ToString(), r["macd"].ToString(), r["macv"].ToString(),r["hs_chucvu"].ToString(),r["tn_vuotkhung"].ToString(),r["tn_nghe"].ToString(),r["hs_khac"].ToString(),"",int.Parse(r["kt_ct"].ToString()));

                    this.Text = "Chuyển Công tác: " + i.ToString() + "/" + rec.ToString() + " dòng";
                    this.Update();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        private void Lylichnv()
        {
            OleDbCommand cmd = new OleDbCommand("select a.*,format(ngaysinh,'dd/mm/yyyy') as m_ngaysinh,format(ngaycap,'dd/mm/yyyy') as ngay_capcmnd from llnv a", con);
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            DataSet ds1 = new DataSet();
            dest.Fill(ds);
            bool gttv;
            int i = 0;
            mDB.Execute_data("delete from nhansu.llnv");
            mDB.Execute_data("delete from nhansu.hopdong");
            //
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                
                try
                {
                    gttv = mDB.Insert_lylichnv(r["manv"].ToString(), r["mnvmay"].ToString(), r["madv"].ToString(), r["stt"].ToString(), r["ho"].ToString().Trim(),
                       r["ten"].ToString().Trim(), r["bidanh"].ToString().Trim(), (r["m_ngaysinh"].ToString() != "" ? r["m_ngaysinh"].ToString() : ""), r["phai"].ToString(),
                        r["noisinh"].ToString().Trim(), r["quequan"].ToString(),
                        r["diachi"].ToString(), r["t_diachi"].ToString(), r["h_diachi"].ToString(),
                        r["noio"].ToString().Trim(), r["t_noio"].ToString(), r["h_noio"].ToString().Trim(), r["dienthoai"].ToString(),
                        r["cmndso"].ToString(), (r["ngay_capcmnd"].ToString() != "" ? r["ngay_capcmnd"].ToString() : ""), r["noicapcmnd"].ToString(), "25",
                        r["matgiao"].ToString(), "01", "", int.Parse(r["namsinh"].ToString()),
                        r["dtdd"].ToString(), r["macd"].ToString(), r["sotk"].ToString(), r["nganhang"].ToString(), r["msthue"].ToString(),"","","");
                    
                      i++;
                     this.Text = "Lý lịch nhân viên: "+i.ToString()+" dòng";
                     this.Update();
                    
                }
                catch(Exception  ee)
                {
                    MessageBox.Show("MA"+r["manv"].ToString()+"\n"+ ee.ToString());
                }
            }
            //

            cmd = new OleDbCommand("select a.*,format(ngaybn,'dd/mm/yyyy') as m_ngaybn  from hopdong a order by manv", con);
            dest = new OleDbDataAdapter(cmd);
            dest.Fill(ds1);
            i = 0;
            foreach (DataRow r1 in ds1.Tables[0].Rows)
            {
              
                mDB.Insert_hopdongLylich(r1["manv"].ToString(), r1["madv"].ToString(), r1["manv"].ToString(), r1["stt"].ToString().PadLeft(5, '0'),
                    (r1["macv"].ToString() != "" ? r1["macv"].ToString() : "999"), r1["c_thich_cv"].ToString().Trim(), r1["m_ngaybn"].ToString(), (r1["tgbn"].ToString() != "" ? int.Parse(r1["tgbn"].ToString()) : 0),decimal.Parse(r1["hs_cv"].ToString()));
                

                i++;
                this.Text = "Hợp đồng nhân viên: " + i.ToString() + " dòng";
                this.Update();
            }
            mDB.Execute_data("update " + mDB.user + ".hopdong set lan=1 ");
        }
        private void dm_ngach()
        {
            mDB.Execute_data("delete from nhansu.mangach ");
            OleDbCommand cmd = new OleDbCommand("select * from mangach order by ma", con);//order by stt
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            int i = 0;
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_DMNGACHCONGCHUC(r["ma"].ToString(), r["diengiai"].ToString(), (r["b1"].ToString() != "" ? decimal.Parse(r["b1"].ToString()) : 0),
                                           (r["b2"].ToString() != "" ? decimal.Parse(r["b2"].ToString()) : 0), (r["b3"].ToString() != "" ? decimal.Parse(r["b3"].ToString()) : 0),
                                           (r["b4"].ToString() != "" ? decimal.Parse(r["b4"].ToString()) : 0), (r["b5"].ToString() != "" ? decimal.Parse(r["b5"].ToString()) : 0),
                                           (r["b6"].ToString() != "" ? decimal.Parse(r["b6"].ToString()) : 0), (r["b7"].ToString() != "" ? decimal.Parse(r["b7"].ToString()) : 0),
                                           (r["b8"].ToString() != "" ? decimal.Parse(r["b8"].ToString()) : 0), (r["b9"].ToString() != "" ? decimal.Parse(r["b9"].ToString()) : 0),
                                           (r["b10"].ToString() != "" ? decimal.Parse(r["b10"].ToString()) : 0), (r["b11"].ToString() != "" ? decimal.Parse(r["b11"].ToString()) : 0),
                                           (r["b12"].ToString() != "" ? decimal.Parse(r["b12"].ToString()) : 0), r["ma"].ToString(), r["loaingah"].ToString(), r["nhomngach"].ToString(), (r["stt"].ToString() != "" ? decimal.Parse(r["stt"].ToString()) : 0));//
                                           //(r["ma"].ToString().Trim()== "" || r["ma1"].ToString()==null  ? r["ma"].ToString() : r["ma1"].ToString().Trim()));
                mDB.Execute_data("update nhansu.mangach set stt=" + r["stt"].ToString() + " where ma='" + r["ma"].ToString()+"'");
                i++;
                this.Text = "Ngạch: " + i.ToString()+"/"+ds.Tables[0].Rows.Count.ToString() + " dòng";
                this.Update();
            }
        }

        private void dm_cd()
        {
            OleDbCommand cmd = new OleDbCommand("select * from dmcd", con);//order by stt
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_DMCHUCDANH(r["ma"].ToString(), r["ten"].ToString());
            }
        }
        private void dm_cvu()
        {
            OleDbCommand cmd = new OleDbCommand("select * from macv", con);//order by stt
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_DMCHUCVU(r["macv"].ToString(), r["diengiai"].ToString(), r["tenvt"].ToString(),decimal.Parse(r["stt"].ToString()),decimal.Parse(r["heso"].ToString()), Convert.ToBoolean(r["lanhdao"].ToString()));
            }
        }
        private void dm_bp()
        {
            OleDbCommand cmd = new OleDbCommand("select * from bophan", con);//order by stt
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                mDB.Insert_DMBOPHAN(r["madv"].ToString(), r["tenbp"].ToString());
            }
        }

        private void cmdkt_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void frmChuyenData_Load(object sender, EventArgs e)
        {
            con = new OleDbConnection(ConSt);
            con.Open();
        }      
        private bool capnhat_HD()
        {
            string n_hdldso = "",sql="", n_loaihd = "", n_ngaydenct = "", n_mangach = "", n_tungay = "", n_denngay = "", n_s_begin = "", n_ngayhd = "", n_ngaybhxh = "", n_ngaynghi = ""
                , n_lydonghi = "", n_matd = "", n_ngayvk = "", n_magc = "", n_heso = "", n_chinhthuc_lv = "", n_nam = "", n_ngay_phep = "", n_tvtungay = "", n_tvdenngay = "", s_mangach = "",n_ngaygiulhd="";
            int n_tbnghihuu = 0, n_bangkhen = 0, n_phepthem = 0, n_ko_hoanthanh = 0;
            int n_bhxh = 0, n_bhyt = 0,n_bhtn=0, n_thangnangluong = 0, n_thaisan = 0, n_thoigian = 0, n_bac = 0, n_lan = 0, n_sonphep = 0,i=0;
            decimal s_hs_lc = 0, n_hs_lc = 0, n_hs_cv = 0, n_hs_tn = 0, n_hs_kv = 0, n_congtacphi = 0, n_sotienhd = 0,n_thuongkhac=0, n_hs_dt = 0,ud = 0, n_hs_th = 0, n_hs_ld = 0, n_hs_db = 0, n_hs_dh = 0, n_luonghd = 0, n_luongct = 0, n_hs_vk = 0;
            bool gttv = false;
            string ghichuct = "";
            sql = "select a.*,format(ngaydenct,'dd/mm/yyyy') as n_ngaydenct,";
            sql += " format(tungay,'dd/mm/yyyy') as n_tungay,format(denngay,'dd/mm/yyyy') as n_denngay, ";
            sql += " format(s_begin,'dd/mm/yyyy') as n_s_begin,format(ngayhd,'dd/mm/yyyy') as n_ngayhd,";
            sql += " format(ngaybhxh,'dd/mm/yyyy') as n_ngaybhxh,format(ngayvk,'dd/mm/yyyy') as n_ngayvk,";
            sql += " format(ngaynghi,'dd/mm/yyyy') as n_ngaynghi";
            sql+=" from hopdong a order by manv";
            OleDbCommand cmd = new OleDbCommand(sql, con);//order by stt
            OleDbDataAdapter dest = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            dest.Fill(ds);

            //try
            //{
                foreach (DataRow r in ds.Tables[0].Rows)
                {

                    try
                    {
                        if (r["manv"].ToString() == "00173")
                        {
                            MessageBox.Show(r["heso"].ToString());
                        }
                        n_magc = (r["magc"].ToString() == "") ? "" : r["magc"].ToString();
                        n_ngaydenct = (r["n_ngaydenct"].ToString() != "") ? r["n_ngaydenct"].ToString() : "";
                        //MessageBox.Show(r["heso"].ToString());
                        n_heso = "";// (r["heso"].ToString() != "") ? r["heso"].ToString().Trim().Substring(r["heso"].ToString().Trim().Length - 1) : "0";
                        n_hdldso = r["hdldso"].ToString();
                        n_matd = r["matd"].ToString();
                        n_loaihd = r["loaihd"].ToString();
                        n_thoigian = (r["thoigian"].ToString() == "0" || r["thoigian"].ToString() == "") ? 0 : int.Parse(r["thoigian"].ToString());
                        n_mangach = r["mangach"].ToString();
                        ghichuct = r["ghichuct"].ToString();
                        n_tungay = r["n_tungay"].ToString();
                        n_denngay = r["n_denngay"].ToString();
                        n_s_begin = r["n_s_begin"].ToString();
                        n_ngayhd = r["n_ngayhd"].ToString();
                        n_luonghd = (r["luonghd"].ToString() == "") ? 0 : decimal.Parse(r["luonghd"].ToString());
                        n_ngaybhxh = r["n_ngaybhxh"].ToString();
                        n_ngayvk = r["n_ngayvk"].ToString();
                        //MessageBox.Show(r["sonphep"].ToString()+" "+r["manv"].ToString());
                        n_sonphep = (r["sonphep"].ToString() != "") ? (int)(decimal.Parse(r["sonphep"].ToString())) : 0;
                        n_bhxh = (r["bhxh"].ToString() != "") ? int.Parse(r["bhxh"].ToString()) : 0;
                        n_bhyt = (r["bhyt"].ToString() != "") ? int.Parse(r["bhyt"].ToString()) : 0;
                        n_bhtn = (r["bhtn"].ToString() != "") ? int.Parse(r["bhtn"].ToString()) : 0;
                        n_hs_lc = (r["hs_lc"].ToString() == "") ? 0 : decimal.Parse(r["hs_lc"].ToString());
                        n_luongct = (r["luongct"].ToString() == "") ? n_luonghd * n_hs_lc : decimal.Parse(r["luongct"].ToString());

                        n_hs_cv = (r["hs_cv"].ToString() == "") ? 0 : decimal.Parse(r["hs_cv"].ToString());
                        n_hs_tn = (r["hs_tn"].ToString() == "") ? 0 : decimal.Parse(r["hs_tn"].ToString());
                        n_hs_kv = (r["hs_kv"].ToString() == "") ? 0 : decimal.Parse(r["hs_kv"].ToString());
                        n_congtacphi = (r["congtacphi"].ToString() == "") ? 0 : decimal.Parse(r["congtacphi"].ToString());
                        n_sotienhd = (r["sotien_hd"].ToString() == "") ? 0 : decimal.Parse(r["sotien_hd"].ToString());
                        n_thuongkhac = (r["thuongkhac"].ToString() == "") ? 0 : decimal.Parse(r["thuongkhac"].ToString());
                        n_hs_dt = (r["hs_dt"].ToString() == "") ? 0 : decimal.Parse(r["hs_dt"].ToString());
                        ud = 0;//(r["hs_ud"].ToString() == "") ? 0 : decimal.Parse(r["hs_ud"].ToString());
                        n_hs_th = (r["hs_th"].ToString() == "") ? 0 : decimal.Parse(r["hs_th"].ToString()); ;
                        n_hs_ld = (r["hs_ld"].ToString() == "") ? 0 : decimal.Parse(r["hs_ld"].ToString());
                        n_hs_db = (r["hs_db"].ToString() == "") ? 0 : decimal.Parse(r["hs_db"].ToString());
                        n_hs_dh = (r["hs_dh"].ToString() == "") ? 0 : decimal.Parse(r["hs_dh"].ToString());
                        n_hs_vk = (r["hs_vk"].ToString() == "") ? 0 : System.Math.Round(decimal.Parse(r["hs_vk"].ToString()) / 100, 3);
                        n_ngaynghi = r["n_ngaynghi"].ToString();
                        n_lydonghi = (r["heso"].ToString().Trim()!="")? r["heso"].ToString().Trim().Substring(r["heso"].ToString().Trim().Length-1,1).PadLeft(2,'0'):"";
                        n_bac = (r["bac"].ToString() == "") ? 0 : int.Parse(r["bac"].ToString());
                        DataSet dsdm = new DataSet();                    
                        string s_madv1, s_macv1;

                        dsdm = mDB.get_data("SELECT * FROM " + mDB.user + ".hopdong");
                        s_madv1 = r["madv"].ToString();

                        s_macv1 = r["macv"].ToString();

                        if (n_loaihd == "BC") mDB.upd_tangbc(r["manv"].ToString(), s_madv1, s_macv1, n_loaihd);

                        s_mangach = r["mangach"].ToString();// == -1) ? "" : cboNCC_HD.SelectedValue.ToString();
                        s_hs_lc = (r["hs_lc"].ToString() != "") ? decimal.Parse(r["hs_lc"].ToString()) : 0;



                        n_lan = 1;
                        n_ngay_phep = n_s_begin;

                        n_tbnghihuu = 0;
                        n_bangkhen = 0;
                        n_ko_hoanthanh = 0;
                        n_phepthem = 0;
                        n_thangnangluong = 0;
                        n_nam = "";
                        n_thaisan = 0;                        

                        //update hopdong
                        gttv = mDB.upd_hopdong(r["manv"].ToString(), r["madv"].ToString(), r["macv"].ToString(), n_hdldso, n_ngayhd, n_loaihd, n_tungay, n_denngay, n_thoigian, n_luonghd, n_ngaydenct,
                            n_ngaynghi, "", "", n_sonphep, n_ngaybhxh, n_s_begin, n_bhyt, n_bhxh,n_bhtn, n_hs_cv, n_hs_tn, n_hs_dh, n_hs_ld, n_hs_db, n_hs_kv,
                            n_hs_lc, n_hs_th, n_luongct, n_mangach, n_lydonghi, n_hs_dt,0, ud, n_ngayvk, n_hs_vk, n_matd, n_magc, "", "", n_bac, n_tbnghihuu,
                            n_bangkhen, n_thangnangluong, n_phepthem, "", "", n_chinhthuc_lv, 0, 0, n_ko_hoanthanh, n_nam, n_thaisan,
                            "", "", n_ngay_phep, n_tvtungay, n_tvdenngay, n_lan, n_heso,n_congtacphi,n_sotienhd,n_ngaygiulhd, ghichuct,n_thuongkhac,"",0,0,0,0,"");

                        i++;
                        this.Text = "Lý lịch nhân viên: " + i.ToString() + " dòng";
                        this.Update();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Ma: " + r["manv"].ToString() + ex.Message);
                        return false;
                    }                    
                }        
            return gttv;
        }

        private void chkHD_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void chkCapso_CheckedChanged(object sender, EventArgs e)
        {

        }
    }
}